package com.reekr.a5t.util;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TablesToClasses {
	
	/*
	 * 连接数据库获取所有表信息
	 */
	private static List<Table> getTables(String driverName, String url, String username, String password) {
		List<Table> tables = new ArrayList<Table>();
		
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Class.forName(driverName);
			con = DriverManager.getConnection(url, username, password);
			//获取所有表名
			//String showTablesSql = "show tables";  //MySQL查询所有表格名称命令
			String showTablesSql = "";
			if(driverName.toLowerCase().indexOf("mysql")!=-1) {
				showTablesSql = "show tables";  //MySQL查询所有表格名称命令
			} else if(driverName.toLowerCase().indexOf("sqlserver")!=-1) {
				showTablesSql = "Select TABLE_NAME FROM edp.INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE'";  //SQLServer查询所有表格名称命令
			} else if(driverName.toLowerCase().indexOf("oracle")!=-1) {
				showTablesSql = "select table_name from user_tables"; //ORACLE查询所有表格名称命令
			}
			ps = con.prepareStatement(showTablesSql);
			rs = ps.executeQuery();
			//循环生成所有表的表信息
			while(rs.next()) {
				tables.add(getTable(rs.getString(1).trim(), con));
			}
			rs.close();
			ps.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return tables;
	}
	
	
	/*
	 * 获取指定表信息并封装成Table对象
	 * @param tableName
	 * @param con
	 */
	private static Table getTable(String tableName, Connection con) throws SQLException {
		Table table = new Table();
		table.setTableName(convertFirstLetterToUpperCase(tableName));
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSetMetaData rsmd = null;
		
		ps = con.prepareStatement("select * from "+tableName);
		rs = ps.executeQuery();
		rsmd = rs.getMetaData();
		int columCount = rsmd.getColumnCount();
		for(int i=1; i<=columCount; i++) {
			table.getColumNames().add(rsmd.getColumnName(i).toLowerCase().trim());
			table.getColumTypes().add(convertType(rsmd.getColumnTypeName(i)));
		}
		
		//System.out.println(table.toString());
		rs.close();
		ps.close();
		return table;
	}
	
	
	/*
	 * 将数据库的数据类型转换为java的数据类型
	 */
	private static String convertType(String mysqlType) {
		String javaType = "";
		
		String mysqlTypeStr = mysqlType.trim().toLowerCase();
		if(mysqlTypeStr.equals("int")) {
			javaType = "Integer";
		} else if(mysqlTypeStr.equals("char")) {
			javaType = "String";
		} else if(mysqlTypeStr.equals("number")) {
			javaType = "Integer";
		} else if(mysqlTypeStr.indexOf("varchar")!=-1) {
			javaType = "String";
		} else if(mysqlTypeStr.equals("blob")) {
			javaType = "Byte[]";
		} else if(mysqlTypeStr.equals("float")) {
			javaType = "Float";
		} else if(mysqlTypeStr.equals("double")) {
			javaType = "Double";
		} else if(mysqlTypeStr.equals("decimal")) {
			javaType = "BigDecimal";
		} else if(mysqlTypeStr.equals("bigint")) {
			javaType = "Long";
		} else if(mysqlTypeStr.equals("date")) {
			javaType = "Date";
		} else if(mysqlTypeStr.equals("time")) {
			javaType = "Time";
		} else if(mysqlTypeStr.equals("datetime")) {
			javaType = "Date";
		} else if(mysqlTypeStr.equals("year")) {
			javaType = "Date";
		} else if(mysqlTypeStr.equals("tinyint")) {
			javaType = "Short";
		} else if(mysqlTypeStr.equals("longblob")) {
			javaType = "Byte[]";
		} else if(mysqlTypeStr.equals("timestamp")) {
			javaType = "Date";
		} else {
			javaType = "[unconverted]" + mysqlType;
		}
		
		return javaType;
	}
	
	
	/*
	 * 生成指定表对象对应的类文件
	 * @param table
	 */
	private static void generateClassFile(Table table) {
		String tableName = table.getTableName();
		List<String> columNames = table.getColumNames();
		List<String> columTypes = table.getColumTypes();
		
		String packageStr = "package com.reekr.a5t.model.activiti;\n\n";
		String importStr = "import java.util.Date;\n\n";
		//生成私有属性和get、set方法
		String propertiesStr = "";  //私有属性字符串
		String getterSetterStr = "";  //get、set方法字符串
		for(int i=0; i<columNames.size(); i++) {
			String columName = columNames.get(i);
			String columType = columTypes.get(i);
			propertiesStr += "\t" + "private " + columType + " " + columName + ";" + "\r\n";
			getterSetterStr += 
				  "\t" + "public " + columType + " "
				+ "get" + convertFirstLetterToUpperCase(columName) + "() {\r\n"
				+ "\t\t" + "return this." + columName + ";\r\n\t}"
				+ "\r\n\r\n"
				+ "\t" + "public void "
				+ "set" + convertFirstLetterToUpperCase(columName)
				+ "(" + columType + " " + columName + ") {\r\n"
				+ "\t\t" + "this." + columName + " = " + columName + ";\r\n\t}"
				+ "\r\n\r\n";
		}
		
		//生成无参构造方法和重写toString方法
		String str1 = "\t" + "public " + tableName + "() {}\r\n" ;  //无参构造方法
		String toStringStr = "\tpublic String toString() { \r\n\t\treturn ";  //toString方法
		for(int i=0; i<columNames.size(); i++) {
			if(i==0) {
				toStringStr += "\"" + columNames.get(i) + ":\" + " + columNames.get(i);
			} else {
				toStringStr += "\", " + columNames.get(i) + ":\" + " + columNames.get(i);
			}
			if(i+1!=columNames.size()) {
				toStringStr += " + ";
			}
		}
		toStringStr += ";\r\n\t}\r\n";
		
		String dir = "/Users/yjl/reekr/git/Activiti5Test/a5t-model/src/main/java/com/reekr/a5t/model/activiti/";
		
		File folder = new File(dir);
		if(!folder.exists()) {
			folder.mkdir();
		}
		
		File classFile = new File(dir+tableName+".java");
		BufferedWriter bw;
		try {
			bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(classFile)));
			bw.write(packageStr);
			bw.write(importStr);
			bw.write("public class "+tableName + " {\r\n");
			bw.write(propertiesStr);
			bw.write("\r\n");
			bw.write(str1);
			bw.write("\r\n");
			bw.write(toStringStr);
			bw.write("\r\n");
			bw.write(getterSetterStr);
			bw.write("}");
			bw.flush();
			bw.close();
		} catch (Exception e) {
			System.out.println("生成类文件("+tableName+")出错！");
			e.printStackTrace();
		} 
		
	}
	
	/*
	 * 首字母大写
	 */
	private static String convertFirstLetterToUpperCase(String letter) {
		return letter.substring(0, 1).toUpperCase() + letter.substring(1, letter.length());
	}
	
	/*
	 * 首字母小写
	 */
	private static String convertFirstLetterToLowerCase(String letter) {
		return letter.substring(0, 1).toLowerCase() + letter.substring(1, letter.length());
	}
	
	
	public static void main(String[] args) {
		List<Table> tables = new ArrayList<Table>();
		
		//MySQL连接
		String driverName = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/a5t_test?autoReconnect=true&useUnicode=true&characterEncoding=utf8";
		String username = "root";
		String password = "";
		
		//SQL Server 链接
//		String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//		String url = "jdbc:sqlserver://localhost:1433;DatabaseName=edp";
//		String username = "sa";
//		String password = "hcl_123";
		
		//ORACLE 链接
//		String driverName = "oracle.jdbc.driver.OracleDriver";
//		String url = "jdbc:oracle:thin:@117.27.144.87:1521:microvideo";
//		String username = "event";
//		String password = "event123";
		
		tables = getTables(driverName, url, username, password);
		System.out.println("Generating...");
		for(int i=0; i<tables.size(); i++) {
			generateClassFile(tables.get(i));
		}
		System.out.println("Generate Success!");
	}
	
}


/**
 * 表格对象
 */
class Table {
	private String tableName;  //表名(首字母大写)
	private List<String> columNames = new ArrayList<String>(); //列名集合
	private List<String> columTypes = new ArrayList<String>();  //列类型集合，列类型严格对应java类型，如String不能写成string，与列名一一对应
	
	public String toString() {
		String tableStr = "";
		tableStr = tableStr + tableName + "\r\n";
		
		//遍历列集合
		for(int i=0; i<columNames.size(); i++) {
			String columName = columNames.get(i);
			String columType = columTypes.get(i);
			tableStr +=  "  " + columName + ":  " + columType + "\r\n";
		}
		
		return tableStr;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public List<String> getColumNames() {
		return columNames;
	}

	public void setColumNames(List<String> columNames) {
		this.columNames = columNames;
	}

	public List<String> getColumTypes() {
		return columTypes;
	}

	public void setColumTypes(List<String> columTypes) {
		this.columTypes = columTypes;
	}
	
}